package com.svms.save.importexcel.service;

import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;
import org.apache.commons.lang3.StringUtils;
import org.springframework.util.ReflectionUtils;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.svms.save.importexcel.utils.ExcelConfig;
import com.svms.save.importexcel.utils.ReflectUtils;
import com.svms.save.importexcel.vo.EntityMapping;
import com.svms.save.importexcel.vo.FieldMapping;
import jxl.Cell;
import jxl.Range;
import jxl.Sheet;
import jxl.Workbook;
import jxl.read.biff.BiffException;
/**
 * 
 * <p>Title      : 吉林省公安厅情报平台</p>
 * <p>Description: TODO[导入Excel]_[excel模版导入]</p>
 * <p>Copyright  : Copyright (c) 2014</p>
 * <p>Company    : 科大讯飞信息科技股份有限公司</p>
 * <p>Department : 行软事业部  公安情报</p>
 * @author       : 杨旭 xuyang2@iflytek.com
 * @version      : 1.0
 */
public abstract class ImportService {
	/**
	 * 验证工具
	 */
	private TestTools testTools;

	public TestTools getTestTools() {
		return testTools;
	}
	public void setTestTools(TestTools testTools) {
		this.testTools = testTools;
	}
	/**
	 * 获取Excel
	 * @param inputStream excel文件流
	 * @return
	 * @throws Exception
	 */
	public Workbook getWorkbook(InputStream inputStream) throws Exception {
		Workbook rwb = null;
		try {
			rwb = Workbook.getWorkbook(inputStream);
		} catch (BiffException e) {
			throw new Exception("格式错误", e);
		} catch (IOException e) {
			throw new Exception(e);
		}
		return rwb;
	}
	
	/**
	 * 解析模版配置的对象
	 * @param wk
	 * @param mappingSheet
	 * @return
	 */
	public List<EntityMapping> getEntityMapping(Workbook wk,
			int mappingSheet) {
		List<EntityMapping> entityMappings = new ArrayList<EntityMapping>();
		Sheet sheet = wk.getSheet(mappingSheet);
		//获取合并的所有单元格
		Range[] ranges = sheet.getMergedCells();
		Cell[] fieldCells = sheet.getRow(ExcelConfig.FIELD_MAPPING_NO);
		for (Range r : ranges) {
			Cell cell = r.getTopLeft();
			String entityStr = cell.getContents();
			int startIndex = cell.getColumn();
			int endIndex = r.getBottomRight().getColumn();
			if (StringUtils.isNotBlank(entityStr)) {
				JSONObject json = JSONObject.parseObject(entityStr);
				EntityMapping entity = JSONObject.toJavaObject(json, EntityMapping.class);
				entity.setStartIndex(startIndex);
				entity.setEndIndex(endIndex);
				List<FieldMapping> fieldList = new ArrayList<FieldMapping>();
				for (; startIndex <= endIndex; startIndex++) {
					Cell fieldCell = fieldCells[startIndex];
					String fieldStr = fieldCell.getContents();
					if (StringUtils.isNotBlank(fieldStr)) {
						JSONObject fieldJson = JSONObject.parseObject(fieldStr);
						FieldMapping field = JSONObject.toJavaObject(
								fieldJson, FieldMapping.class);
						fieldList.add(field);
					}
				}
				entity.setFieldList(fieldList);
				entityMappings.add(entity);
			}
		}
		return entityMappings;
	}
	/**
	 * 根据映射配置解析Excel中的数据
	 * @param wk excel
	 * @param emList 映射配置
	 * @return
	 */
	public JSONObject readDataByEntityMapping(Workbook wk,List<EntityMapping> emList){
		JSONObject jsonResult = new JSONObject();
		JSONArray jsonArray = new JSONArray();
		Sheet sheet = wk.getSheet(ExcelConfig.DATA_SHEET_NO);
		int rowNo = sheet.getRows();
		boolean allTestIsOk = true;
		String allMsg = "";
		for(EntityMapping em : emList){
			if((rowNo-ExcelConfig.DATA_CELL_NO) > ExcelConfig.IMPORTMAXSIZE){
				allTestIsOk = false;
				allMsg = "每次最多可以导入"+ExcelConfig.IMPORTMAXSIZE+"条数据！";
				break;
			}
			int startIndex = em.getStartIndex();
			int endIndex = em.getEndIndex();
			List dataList = new ArrayList();
			for(int i=ExcelConfig.DATA_CELL_NO;i<rowNo;i++){
				Cell[] cells = sheet.getRow(i);
				int rowNumber = i+1;
				int fieldMappIdx = 0;
				Object object = ReflectUtils.newInstanceByMapping(em.getClassName());
				for(int j=startIndex;j <= endIndex;j++,fieldMappIdx++){
					String cellValue = null; 
					if(j>=cells.length){
						cellValue = "";
					}else{
						cellValue = cells[j]==null?"":cells[j].getContents();
					}
					FieldMapping fm = em.getFieldList().get(fieldMappIdx);
					JSONObject testJson = testData(fm,cellValue.trim(),cells);
					boolean flag = (Boolean) testJson.get(ExcelConfig.JSON_FLAG_KEY);
					if(!flag){
						allTestIsOk = false;
						String msg = (String) testJson.get(ExcelConfig.JSON_MSG_KEY);
						int cellNumber = j +1;
						msg ="第"+rowNumber+"行， 第"+cellNumber+"列，"+msg;
						testJson.put(ExcelConfig.JSON_MSG_KEY, msg);
					}else{
						if(allTestIsOk){
							ReflectUtils.invokeSet(object, fm.getFieldName(), new Object[]{cellValue.trim()});
						}
					}
					jsonArray.add(testJson);
				}
				dataList.add(object);
				em.setDataList(dataList);
			}
		}
		if(!allTestIsOk && allMsg.equals("")){
			allMsg = "导入的数据项不符合规范！";
		}
		jsonResult.put(ExcelConfig.JSON_ALLFLAG_KEY, allTestIsOk);
		jsonResult.put(ExcelConfig.JSON_ITEMS_KEY, jsonArray);
		jsonResult.put(ExcelConfig.JSON_MSG_KEY, allMsg);
		return jsonResult;
	}
	/**
	 * 数据验证
	 * @param fm 字段映射对象
	 * @param value 值
	 * @return
	 */
	public JSONObject testData(FieldMapping fm,String value,Cell[] cells){
		JSONObject json = new JSONObject();
		//非空验证
		if(fm.getIsNotBlank()){
			if(StringUtils.isBlank(value)){
				json.put(ExcelConfig.JSON_FLAG_KEY, false);
				json.put(ExcelConfig.JSON_MSG_KEY,ExcelConfig.TESTDATA_ERROR_ISBLANK_MSG);
				return json;
			}
		}
		//字符串长度验证
		if(StringUtils.isNotBlank(value)){
			if(!testTools.lengthTest(fm.getLength(), value)){
				json.put(ExcelConfig.JSON_FLAG_KEY, false);
				json.put(ExcelConfig.JSON_MSG_KEY,ExcelConfig.TESTDATA_ERROR_LENGTH_MSG);
				return json;
			}
		}
		boolean flag =  true;
		String msg = "";
		if(StringUtils.isNotBlank(value)){
			switch (fm.getTestType()){
				//字典
				case ExcelConfig.TESTTYPE_DIC:
					flag = testTools.dicTest(fm.getDicName(), value);
					msg = flag?"":ExcelConfig.TESTDATA_ERROR_DIC_MSG;
					break;
				//正则表达式
				case ExcelConfig.TESTTYPE_REG:
					flag = testTools.regTest(fm.getRegValue(), value);
					msg = flag?"":ExcelConfig.TESTDATA_ERROR_REG_MSG;
					break;
				//身份证
				case ExcelConfig.TESTTYPE_SFZH:
					flag = testTools.IDCardValidate(value);
					msg = flag?"":ExcelConfig.TESTDATA_ERROR_SFZH_MSG;
					break;
				//自定义验证
				case ExcelConfig.TESTTYPE_OTHER:
					String testRes = testTools.otherTest(fm, value, cells);
					flag = StringUtils.isBlank(testRes);
					msg = testRes;
					break;
			}
		}
		json.put(ExcelConfig.JSON_FLAG_KEY, flag);
		json.put(ExcelConfig.JSON_MSG_KEY, msg);
		return json;
	}
}
